﻿Imports System.Data.OleDb
Public Class ExcelProvider
    Public Shared sql As String = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=D:\tam1.xls ;Extended  Properties ='Excel 8.0; HDR=YES;'"

    Public Shared file_name As String = ""
    Public Shared Function connecttion() As OleDb.OleDbConnection

        Dim cn As OleDbConnection = New OleDbConnection()

        Dim sql As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES""", file_name)
        cn.ConnectionString = sql
        Return cn
    End Function
    Public Shared Sub NonQuery(ByVal sql As String)

        Dim cn As OleDb.OleDbConnection = ExcelProvider.connecttion()
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql, cn)
        cmd.Connection.Open()
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()

    End Sub

    Public Shared Function DataAdapter(ByVal sql As String)

        Dim cn As OleDb.OleDbConnection = ExcelProvider.connecttion()
        cn.Open()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, cn)
        Dim dsLop As DataTable = New DataTable()
        da.Fill(dsLop)
        cn.Close()
        Return dsLop

    End Function

    Public Shared Function laybang(ByVal sql As String) As DataTable
        Dim cnn As OleDbConnection = New OleDbConnection()
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='QLNhaHang.mdb'"
        cnn.Open()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, cnn)
        Dim dsLop As DataTable = New DataTable()
        da.Fill(dsLop)
        cnn.Close()
        Return dsLop
    End Function

End Class
